package com.iris.live.services.common.reconsitution;

import com.google.common.collect.Maps;
import com.iris.live.services.common.DateFormatUtils;
import com.iris.live.services.common.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.NotOfficeXmlFileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 读取excel
 */
public class ExcelAnalysisUtil {
    public static final String  PRIMARY ="primaryKey";//参数：主键列
    public static final String  TITLE_LINE ="titleLine";//参数：标题行数
    public static final String  CELL_LINE ="cellLine";//参数：标题列数
    public static final String  XML_NAME ="StockConfig.xml";//映射模板配置文件名称
    public static final String  XML_EXCEL_NAME ="name";//映射模板用户对应列表标示
    public static final String  XML_EXCEL_DBNAME ="dbname";//映射模板实体对应标示
    public static final String  XML_TYPE_PARAM ="fileTypeName";//映射模板实体对应标示
    public static final String  EXCEL_TYPE_2003 ="xls";//检测返回message失败个数
    public static final String  EXCEL_TYPE_2007 ="xlsx";//检测返回message失败个数
    public static final String  VALID_EXCEL_ERROR ="上传文件格式不是excel！";//检测返回excel格式文本
    public static final String  VALID_RES_FAIL ="模板数据匹配失败条数：";//检测返回message失败个数
    public static final String  VALID_RES_SUSS ="模板数据匹配成功条数：";//检测返回message成功个数
    public static final String  VALID_RES_ERROR ="缺少下列必要字段：%s请完善导入模板中对应数据后再次尝试！";//检测返回message文本
    public static final String  INIT_XML_FAIL ="StockConfig.xml Init Fail！";//xml初始化测失败
    public static final String  INIT_XML_FIRST_TITLE_LINE_FAIL ="StockConfig.xml First Row TitleLine Init Fail！";//titleLine初始化失败
    public static final String KEY_STATUS = "status"; //状态
    public static final String KEY_VALID = "valid"; //验证
    public static final String KEY_LIST = "list"; //数据集合
    public static final String KEY_STR = "str"; //VALID_RES_ERROR 文本
    public static final String XML_INDEX = "index";//映射模板的顺序标识
    public static final String XML_NODENAME_CAR = "modelsContrast";//车辆对比xml的节点名称
    public static final String XML_NODENAME_BC_FINANCIALINTEREST = "gallopStock";//奔驰金融贴息xml的节点名称
    public static final String XML_NODENAME_SMART_FINANCIALINTEREST = "smartFinancialInterest";//smart金融贴息xml的节点名称
    public static final String XML_NODENAME_BC_ENJOY = "BCEnjoy";//奔驰先享后选xml的节点名称
    public static final String XML_NODENAME_SMART_ENJOY = "smartEnjoy";//smart先享后选xml的节点名称
    public static final String XML_NODENAME_AMG_FINANCEDISCOUNT = "AMGFinanceDiscount";//AMG金融贴息xml的节点名称
    public static final String XML_NODENAME_AMG_ENJOY = "AMGEnjoy";//AMG先享后选xml的节点名称
    public static  final String CONFIG_ODE_NAME_DACHANG_STOCK="daChangStock";//大昌库存
    public static final String XML_NODENAME_ILOG_TRANSFER_LEADINGIN = "ilogTransferLeadingIn";//数据迁移导入名单xml的节点名称
    public static final String XML_NODENAME_ILOG_TRANSFER_CUSRECORDPOOL = "ilogTransferCusRecordPool";//数据迁移顾客记录汇总xml的节点名称
    public static final String XML_NODENAME_ILOG_TRANSFER_CUSINTOTHESTOREANDTESTDRIVE = "ilogTransferCusIntoTheStoreAndTestDrive";//数据迁移接待记录xml的节点名称
    public static final int  PRIMARY_NULL_LINE = 3;//主键为空的时候往下继续读取次数达到当前设置结束读取数据
    public static List<Map<String, Object>> xmlList = null;//映射模板集合
    public static String ERRORMESSAGE = "errorMessage";    // 失败提示语
    public static String ISSUCCESS = "isSuccess";      // 是否成功
    /**
     * 初始化为了（可移植性牵扯文件不便多增）
     */
    private static Logger logger = LoggerFactory.getLogger("Excel");

    /**
     * 检测用户文件数据是否完整(应为同一文件流第二次读取异常的问题(文件流不能重复读取)暂时在检索的时候同步返回数据（仅成功匹配后）)
     * @param file
     * @return
     * @throws IOException
     */
    public static Map<String,Object> validExcel(MultipartFile file,String nodeName)throws IOException {
        //处理文件对象
        InputStream inputStream = file.getInputStream();
        String filename = file.getOriginalFilename();//取得文件原名
        Integer indexStar = filename.lastIndexOf(".") +1;//取得文件后缀名
        String excelTypeName = filename.substring(indexStar);//取得文件后缀名
        if(!StringUtils.isNullOrBlank(nodeName)){
            xmlList = XmlAnalysisUtil.analysisXmlList(ExcelAnalysisUtil.XML_NAME,nodeName);
        }
        if(EXCEL_TYPE_2007.equals(excelTypeName)){
            return (Map<String,Object>)validAndGetCusExcelList2007(inputStream,excelTypeName);
        }
        return (Map<String,Object>)validAndGetCusExcelList2003(inputStream,excelTypeName);
    }

    /**
     * iLog用的Excel验证方法
     *
     * @param file
     * @param nodeName
     * @return
     * @throws IOException
     */
    public static Map<String,Object> validExcelFile(File file, String nodeName)throws IOException {
        //处理文件对象
        InputStream inputStream = new FileInputStream(file.getAbsoluteFile());
        String filename = file.getName();//取得文件原名
        Integer indexStar = filename.lastIndexOf(".") +1;//取得文件后缀名
        String excelTypeName = filename.substring(indexStar);//取得文件后缀名
        if(!StringUtils.isNullOrBlank(nodeName)){
            xmlList = XmlAnalysisUtil.analysisXmlList(ExcelAnalysisUtil.XML_NAME,nodeName);
        }
        if(EXCEL_TYPE_2007.equals(excelTypeName)){
            return (Map<String,Object>)validAndGetCusExcelList2007(inputStream,excelTypeName);
        }
        return (Map<String,Object>)validAndGetCusExcelList2003(inputStream,excelTypeName);
    }


    /**
     * 读取sheet数据集合(逐个读取cell)  (暂时没用)
     * @param first
     * @return
     */
    private static List<Map<String,Object>> getSheetExcel(Sheet first){
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        //取得用户模板标题以及列的下标
        Map<String,Object> title = Maps.newHashMap();
        Integer titleLine =0;
        Integer cellLine =0;
        String primaryKey = null;
        if(initXmlRes()) {
            titleLine = Integer.parseInt(xmlList.get(0).get(ExcelAnalysisUtil.TITLE_LINE).toString()) - 1;
            if(xmlList.get(0).containsKey(ExcelAnalysisUtil.CELL_LINE)) {       //防止xml中没有填写cell_line属性
                cellLine = Integer.parseInt(xmlList.get(0).get(ExcelAnalysisUtil.CELL_LINE).toString());
            }
        }
        int rowSize = first.getLastRowNum() + 1;
        for (int i = titleLine; i < rowSize; i++) {
            Row row = first.getRow(i);//获取当前行
            if(row==null||row.getFirstCellNum()==-1){
                continue;
            }
            int cellSize = cellLine==0?row.getLastCellNum():cellLine;//行中有多少个单元格，也就是有多少列
            Map<String,Object> data =null;
            for (int j = 0; j < cellSize; j++) {
                //读取标题
                if(i == titleLine){
                    if(null !=row.getCell(j)){
                        title.put(row.getCell(j)!=null?row.getCell(j).toString():"",j);
                    }
                //读取数据
                }else if( i > titleLine){
                    //如果映射模板无法加载直接从excel读取
                    if(!initXmlRes()){
                        data =  buildMapExcel(row,title);
                    }else{
                        data =  buildMap(row,title);
                    }
                }
            }
            //载入数据
            if(null != data && data.size() > 0){
                //检测到停止命令直接返回
                if(data.containsKey("stopRead")){
                    return list;
                }
                list.add(data);
            }
        }
        return list;
    }

    /**
     * 读取sheet数据集合(指定行内列下标读取)
     * @param first
     * @return
     */
    private static Map<String,Object> getSheetExcelIndex(Sheet first){
        Map<String,Object> map = new HashMap<>();
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        //取得用户模板标题以及列的下标
        Map<String,Object> title = Maps.newHashMap();
        int nullCellNum = 0;  // 空行累计
        Integer titleLine =0;
        Integer cellLine =0;
        String primaryKey = null;
        String message = "";
        boolean flag = true;
        if(initXmlRes()) {
            titleLine = Integer.parseInt(xmlList.get(0).get(ExcelAnalysisUtil.TITLE_LINE).toString()) - 1;
            if(xmlList.get(0).containsKey(ExcelAnalysisUtil.CELL_LINE)) {       //防止xml中没有填写cell_line属性
                cellLine = Integer.parseInt(xmlList.get(0).get(ExcelAnalysisUtil.CELL_LINE).toString());
            }
        }
        int rowSize = first.getLastRowNum() + 1;
        for (int i = titleLine; i < rowSize; i++) {
            Row row = first.getRow(i);//获取当前行
            if(row==null||row.getFirstCellNum()==-1){
                continue;
            }
            int cellSize = cellLine==0?row.getLastCellNum():cellLine;//行中有多少个单元格，也就是有多少列
            Map<String,Object> data =null;//当前行数据
            //读取标题
            if(i == titleLine){
                for (int j = 0; j < cellSize; j++) {
                    if(null !=row.getCell(j)){
                        String name=row.getCell(j).toString();
                          name=  name.replace("\n","");
                            title.put(name,j);
                    }else {
                        title.put("",j);
                    }
                }
            }else if(i > titleLine){
                //载入数据
                //如果映射模板无法加载直接从excel读取
                if(!initXmlRes()){
                    data =  buildMapExcel(row,title);
                }else{
                    data =  buildMap(row,title);
                }
                if (data != null) {
                    data.put("row",i);  // 放入行号
                    data.put("titleLine",titleLine);  // 放入标题行行号

                    //  设置错误提示信息
                    if (!StringUtils.isNullOrBlank(data.get(ISSUCCESS)) && !(Boolean) data.get(ISSUCCESS)) {
                        flag = false;
                        message = message + data.get(ERRORMESSAGE).toString();
                    }
                }

                //空行检测
                if(!(Boolean) data.get("isEmptyRow")){
                    list.add(data);
                    nullCellNum =0;//重置连续空行数
                }else{
                    //检测到连续空行记录累加
                    nullCellNum ++;
                    //连续空行达到伐值停止读取
                    if(nullCellNum >=ExcelAnalysisUtil.PRIMARY_NULL_LINE){
                       break;
                    }
                }
            }
        }
        if (!flag) {
            map.put(ISSUCCESS,"false");
            map.put(ERRORMESSAGE,message);
        } else {
            map.put(ISSUCCESS,"true");
            map.put(ERRORMESSAGE,"");
        }
        map.put("dataList",list);
        return map;
    }



    /**
     * 验证映射模板必须项
     * @param first
     * @return
     */
    private static Map<String,Object> validExcelTitle(Sheet first){
        //返回结果
        Map<String,Object> res = Maps.newHashMap();
        //取得用户模板标题以及列的下标
        Map<String,Object> title = Maps.newHashMap();
        Integer titleLine =0;
        Integer cellLine =0;
        if(initXmlRes()){
            titleLine = Integer.parseInt(xmlList.get(0).get(ExcelAnalysisUtil.TITLE_LINE).toString()) - 1;
            if(xmlList.get(0).containsKey(ExcelAnalysisUtil.CELL_LINE)) {       //防止xml中没有填写cell_line属性
                cellLine = Integer.parseInt(xmlList.get(0).get(ExcelAnalysisUtil.CELL_LINE).toString());
            }
        }
        Row row = first.getRow(titleLine);//获取当前行
        int cellSize = cellLine==0?row.getLastCellNum():cellLine;//行中有多少个单元格，也就是有多少列
        Map<String,Object> data =null;
        for (int j = 0; j < cellSize; j++) {
            if(null !=row.getCell(j)){
                String name=row.getCell(j).toString();
                name=  name.replace("\n","");
                title.put(name,j);
            }
        }
        res = compareExcelTitle(row,title);
        return res;
    }


    /**
     * 按映射模板抽取一条/行数据
     * @param row
     * @param title
     * @return
     */
    private static Map<String,Object> buildMap(Row row, Map<String,Object> title){
        Map<String,Object>  stock = Maps.newHashMap();
        Integer cellIndex = 0;
        String excelTitle =null;
        String  primaryKey = xmlList.get(0).get(ExcelAnalysisUtil.PRIMARY).toString();
        String message = ""; // 格式校验错误提示语
        boolean isSuccess = true; // 是否成功
        boolean keyError = false; // 主键是否有误
        boolean isEmptyRow = true; //是否为空行
        Integer titleLine = 0;
        titleLine = Integer.parseInt(xmlList.get(0).get(ExcelAnalysisUtil.TITLE_LINE).toString());
        // 是否为空行
        for (Map<String,Object> map : xmlList) {
            excelTitle = map.get(ExcelAnalysisUtil.XML_EXCEL_NAME).toString().trim();
            cellIndex = Integer.parseInt(title.get(excelTitle).toString());
            if (null != row.getCell(cellIndex) && row.getCell(cellIndex).getCellType() != Cell.CELL_TYPE_BLANK && !"".equals(row.getCell(cellIndex).toString())) {
                isEmptyRow = false;
                break;
            }
         }

        //  不是空行,则校验主键是否填写
        if (!isEmptyRow) {
            for (Map<String,Object> map : xmlList) {
                excelTitle = map.get(ExcelAnalysisUtil.XML_EXCEL_NAME).toString().trim();
                cellIndex = Integer.parseInt(title.get(excelTitle).toString());
                // 如果key为空，则返回提示信息
                if (null == row.getCell(cellIndex) || "".equals(row.getCell(cellIndex).toString())) {
                    if (validPkIsNull(excelTitle)) {
                        message = message + "第" + (row.getRowNum()+1-titleLine) + "行" + "第" + cellIndex + "列" + "不能为空；";
                        isSuccess = false;
                        keyError = true;
                        break;
                    }
                }
            }
        }
        else keyError = true;  // 空行，则主键必定为空

        // 主键不为空
        if (!keyError) {
            for (Map<String,Object> map : xmlList) {
                try{
                    excelTitle = map.get(ExcelAnalysisUtil.XML_EXCEL_NAME).toString().trim();
                    if (map.containsKey(ExcelAnalysisUtil.XML_INDEX)) {
                        cellIndex = Integer.parseInt(map.get(ExcelAnalysisUtil.XML_INDEX).toString());
                        //校验表格中单元格的格式
                        boolean falg = checkFormat(row.getCell(cellIndex),map);
                        if (!falg) {
                            message = message + "第" + (row.getRowNum()+1-titleLine) + "行" + "第" + cellIndex + "列" + "格式错误；";
                            isSuccess = false;
                            continue;
                        }
                        stock.put(map.get(ExcelAnalysisUtil.XML_EXCEL_DBNAME).toString(), formatDataOfMap(row.getCell(cellIndex),map));
                    } else {
                        if (title.containsKey(excelTitle)) {
                            cellIndex = Integer.parseInt(title.get(excelTitle).toString());
                            //校验表格中单元格的格式
                            boolean falg = checkFormat(row.getCell(cellIndex),map);
                            if (!falg) {
                                message = message + "第" + (row.getRowNum()+1-titleLine) + "行" + "第" + cellIndex + "列" + "格式错误；";
                                isSuccess = false;
                                continue;
                            }
                            //存入映射中配置的实体名称和当前
                            if (null == row.getCell(cellIndex) || "".equals(row.getCell(cellIndex).toString())) {
                                stock.put(map.get(ExcelAnalysisUtil.XML_EXCEL_DBNAME).toString(), "");
                            }else{
                                stock.put(map.get(ExcelAnalysisUtil.XML_EXCEL_DBNAME).toString(), formatDataOfMap(row.getCell(cellIndex),map));
                            }
                        }
                    }
                }catch (Exception e){
                    logger.error(e.getMessage());
                }
            }
        }

        if (!isSuccess) {
           stock.put(ERRORMESSAGE,message + "请修改。");
        } else {
            stock.put(ERRORMESSAGE,"");
        }
        stock.put("isEmptyRow",isEmptyRow);
        stock.put(ISSUCCESS,isSuccess);
        return stock;
    }

    /**
     * 直接读取一条/行数据
     * @param row
     * @param title
     * @return
     */
    private static Map<String,Object> buildMapExcel(Row row, Map<String,Object> title){
        Map<String,Object>  stock = Maps.newHashMap();
        Integer cellIndex = 0;
        String excelTitle =null;
        try{
            Set set = title.entrySet();
            Iterator i = set.iterator();
            while(i.hasNext()){
                Map.Entry<String, String> entry1=(Map.Entry<String, String>)i.next();
                cellIndex = Integer.parseInt(title.get(entry1.getKey()).toString());
                if(null == row.getCell(cellIndex)){
                    stock.put(entry1.getKey(), "");
                }
                stock.put(entry1.getKey(), formatData(row.getCell(cellIndex)));
            }
        }catch (Exception e){
            logger.error(e.getMessage());
        }
        return stock;
    }


    /**
     * 匹配用户和模板差异
     * @param row
     * @param title
     * @return
     */
    private static Map<String,Object> compareExcelTitle(Row row, Map<String,Object> title){
        Map<String,Object> res = Maps.newHashMap();
        StringBuilder sb = new StringBuilder();
        Integer failNum=0;
        //如果映射模板无法加载不参加比对
        if(!initXmlRes()){
            res.put(KEY_STATUS,true);
            res.put(KEY_STR,"");
            return res;
        }
        for (Map<String,Object> map : xmlList) {
            String excelTitle = map.get(ExcelAnalysisUtil.XML_EXCEL_NAME).toString().trim();
            if (!map.containsKey(ExcelAnalysisUtil.XML_INDEX)) {
                if (!title.containsKey(excelTitle)) {
                    sb.append(excelTitle + ",");
                    failNum = failNum + 1;
                }
            }
        }
        res.put(KEY_STATUS,failNum ==0?true:false);
        res.put(KEY_STR,String.format(VALID_RES_ERROR,sb.toString()));
        return res;
    }


    /**
     * 模板是否初始化成功
     * @return
     */
    private static Boolean initXmlRes(){
        //获取映射配置xml
        if (null == xmlList || xmlList.size() <= 0){
            logger.error(INIT_XML_FAIL);
            return false;
        }
        //标题下标
        if(!StringUtils.isNumeric(xmlList.get(0).get(ExcelAnalysisUtil.TITLE_LINE).toString())){
            logger.error(INIT_XML_FIRST_TITLE_LINE_FAIL);
            return false;
        }
        return true;
    }

    /**
     * 验证主键列处理
     * @param excelTitle
     */
    private static boolean validPkIsNull(String excelTitle){
        if (xmlList.get(0).get(ExcelAnalysisUtil.PRIMARY).toString().equals(excelTitle)) {
           return true;
        }else{
            return false;
        }
    }


    /**
     * 2003版本验证同步返回数据集合
     * @param inputStream
     * @param excelTypeName
     * @return
     * @throws IOException
     */
    private static Map<String,Object> validAndGetCusExcelList2003(InputStream inputStream,String excelTypeName) throws IOException {
        Map<String,Object> mapRes = Maps.newHashMap();
        mapRes.put(ISSUCCESS,"true");
        Sheet first =null;
        if(EXCEL_TYPE_2003.equals(excelTypeName)){
            HSSFWorkbook hwb = null;
            try {
                hwb = new HSSFWorkbook(inputStream);
            } catch (IOException e) {
                logger.error("当前表格被另一程序打开或使用，请关闭该程序。",e);
                mapRes.put(ERRORMESSAGE,"当前表格被另一程序打开或使用，请关闭该程序。");
                mapRes.put(ISSUCCESS,"false");
                return mapRes;
            }
            first = hwb.getSheetAt(0);
            Map<String,Object> valid = validExcelTitle(first);
            Boolean status =  valid.get(KEY_STATUS) ==null?false:Boolean.parseBoolean(valid.get("status").toString());
            if(status){
                Map<String,Object> map = getSheetExcelIndex(first);
                List<Map<String,Object>> mapList = (List<Map<String,Object>>) map.get("dataList");
                mapRes.put(ISSUCCESS,map.get(ISSUCCESS));
                mapRes.put(ERRORMESSAGE,map.get(ERRORMESSAGE));
                mapRes.put(KEY_LIST,mapList);
                mapRes.put(XML_TYPE_PARAM,excelTypeName);
            } else {
                mapRes.put(ISSUCCESS,"false");
                mapRes.put(ERRORMESSAGE,"导入的模板不正确");
                logger.error("导入的模板不正确");
            }
            mapRes.put(KEY_VALID,valid);
        }else{
            logger.error(VALID_EXCEL_ERROR);
        }
        return mapRes;
    }

    /**
     * 2007版本验证同步返回数据集合
     * @param inputStream
     * @param excelTypeName
     * @return
     * @throws IOException
     */
    private static Map<String,Object> validAndGetCusExcelList2007(InputStream inputStream,String excelTypeName) throws IOException {
        Map<String,Object> mapRes = Maps.newHashMap();
        mapRes.put(ISSUCCESS,"true");
//        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        if(EXCEL_TYPE_2007.equals(excelTypeName)){
            XSSFWorkbook workbook1 = null;
            try {
                workbook1 = new XSSFWorkbook(inputStream);
            } catch (NotOfficeXmlFileException e) {
                logger.error("当前表格被另一程序打开或使用，请关闭该程序。",e);
                mapRes.put(ERRORMESSAGE,"当前表格被另一程序打开或使用，请关闭该程序。");
                mapRes.put(ISSUCCESS,"false");
                return mapRes;
            }
            XSSFSheet sheet = workbook1.getSheetAt(0);
            Map<String,Object> valid = validExcelTitle(sheet);
            Boolean status =  valid.get(KEY_STATUS) ==null?false:Boolean.parseBoolean(valid.get("status").toString());
            if(status){
                Map<String,Object> map = getSheetExcelIndex(sheet);
                List<Map<String,Object>> mapList = (List<Map<String,Object>>) map.get("dataList");
                mapRes.put(ISSUCCESS,map.get(ISSUCCESS));
                mapRes.put(ERRORMESSAGE,map.get(ERRORMESSAGE));
                mapRes.put(KEY_LIST,mapList);
                mapRes.put(XML_TYPE_PARAM,excelTypeName);
            } else {
                mapRes.put(ISSUCCESS,"false");
                mapRes.put(ERRORMESSAGE,"导入的模板不正确。");
                logger.error("导入的模板不正确。");
            }
            mapRes.put(KEY_VALID,valid);
        }else{
            logger.error(VALID_EXCEL_ERROR);
        }
        return mapRes;
    }

    // 数据格式转换
    private static String formatData(Cell cell) throws  Exception {
        String cellvalue = cell.toString();
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC :
            {
                int dataFormat = cell.getCellStyle().getDataFormat();
                // 日期格式
                if (dataFormat == 14 || dataFormat == 31 || dataFormat == 177 || dataFormat == 179 || dataFormat == 180 || dataFormat == 181 || dataFormat == 182) {
                    // 乱码日期格式转为“yyyy-MM-dd”日期格式
                    Date date = cell.getDateCellValue();
                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                    cellvalue = DateFormatUtils.getFormatDateToString(date, DateFormatUtils.TIME_FORMAT_STR_YYYY_MM_DD);
                }
                // 科学计数法的数字(1.5169935777E10 转为 15169935777)
                else if (cellvalue.contains("e") || cellvalue.contains("E")){
                    // 把科学计数法转换为整数(会舍弃小数部分)
                    cellvalue = new DecimalFormat("#").format(cell.getNumericCellValue());
                }
                // 普通数字,直接返回字符
                else {
                }
            }
            break;

            // 字符格式（包括Excel中设置成文本类型的日期字符串:"2017-05-05"）,直接返回字符
            case HSSFCell.CELL_TYPE_STRING :  break;

            // 公式格式
            case HSSFCell.CELL_TYPE_FORMULA:
                try {
                    cellvalue = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    cellvalue = String.valueOf(cell.getRichStringCellValue());
                }
                break;

        }
        return cellvalue;
    }

    // 数据格式转换(map)
    private static String formatDataOfMap(Cell cell,Map<String,Object> map) throws  Exception {
        String cellvalue = cell.toString();
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC :
            {
                // 日期格式
                String dataFormat = cell.getCellStyle().getDataFormatString(); //获取日期格式
                if (("yyyy/mm;@".equals(dataFormat) || "m/d/yy".equals(dataFormat)
                        || "yy/m/d".equals(dataFormat) || "mm/dd/yy".equals(dataFormat)
                        || "dd-mmm-yy".equals(dataFormat)|| "yyyy/m/d".equals(dataFormat))
                        ||(null != map.get("formatType") && "date".equals(map.get("formatType").toString()))) {
                    // 乱码日期格式转为“yyyy-MM-dd”日期格式
                    Date date = cell.getDateCellValue();
                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                    cellvalue = DateFormatUtils.getFormatDateToString(date, DateFormatUtils.TIME_FORMAT_STR_YYYY_MM_DD);
                }
                // 科学计数法的数字(1.5169935777E10 转为 15169935777)
                else if (cellvalue.contains("e") || cellvalue.contains("E")){
                    // 把科学计数法转换为整数(会舍弃小数部分)
                    cellvalue = new DecimalFormat("#").format(cell.getNumericCellValue());
                }
                // 普通数字,直接返回字符
                else {
                }
            }
            break;

            // 字符格式（包括Excel中设置成文本类型的日期字符串:"2017-05-05"）,直接返回字符
            case HSSFCell.CELL_TYPE_STRING :  break;

            // 公式格式
            case HSSFCell.CELL_TYPE_FORMULA:
                try {
                    cellvalue = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    cellvalue = String.valueOf(cell.getRichStringCellValue());
                }
                break;

        }
        return cellvalue;
    }

    private static  boolean checkFormat(Cell cell,Map<String,Object> map) {
        if (map.get("formatType") == null || StringUtils.isNullOrBlank(cell.toString())) return true;
        // 日期类型
        if ("date".equals(map.get("formatType").toString())) {
            if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {  // “yyyy-MM-dd“格式的字符串,也可以通过日期验证
                try {
                    Date value = new SimpleDateFormat("yyyy-MM-dd").parse(cell.toString());
                } catch (Exception e) {
                    return false;
                }
            } else {
                try {
                    Date value = cell.getDateCellValue();
                } catch (Exception e) {
                    return false;
                }
            }
        }
        // 数值类型
        else if("number".equals(map.get("formatType"))){
            boolean flag = false;
            flag = cell.toString().matches("-?[0-9]+.*[0-9]*");  // 数字（包括小数和正负数）
            if (!flag) return false;
        }
        return true;
    }

    // 检测是否为空行
    private static boolean isEmptyRow (Row row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
                return false;
        }
        return true;
    }

}
